﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using MetaDataRepository;


namespace TestRepository
{
    class SQLDBEnumerator
    {
        private MetaDataRepository.Repository repository;
        private mdExecution Execution;
        private SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder();

        public SQLDBEnumerator()
        {}

        public bool Initialize(MetaDataRepository.Repository repository, mdExecution execution)
        {
            bool success;

            try
            {
                this.repository = repository;
                this.Execution = execution;
                success = true;
            }
            catch (System.Exception ex)
            {
                Console.WriteLine(string.Format("Could not initialize the Database Enumerator: {0}", ex.Message));
                success = false;
            }

            return success;
        }

        public void EnumerateDatabase(string dbConnection)
        {
            string serverName = string.Empty;
            string loginName = string.Empty;
            string password = string.Empty;
            Server sqlServer;
            ServerConnection sqlConnection;
            Database sqlDatabase;

            connectionStringBuilder.Clear();
            connectionStringBuilder.ConnectionString = dbConnection;

            try
            {
                sqlConnection = new ServerConnection(connectionStringBuilder.DataSource);
                sqlConnection.ApplicationName = "DependencyAnalyzer";
                
                if (connectionStringBuilder.IntegratedSecurity)
                {
                    sqlConnection.LoginSecure = true;
                }
                else
                {
                    sqlConnection.LoginSecure = false;
                    sqlConnection.Login = connectionStringBuilder.UserID;
                    sqlConnection.Password = connectionStringBuilder.Password;
                }
                sqlServer = new Server(sqlConnection);
                sqlServer.SetDefaultInitFields(typeof(Table), "Name");
                sqlServer.SetDefaultInitFields(typeof(Table), "Schema");
                sqlServer.SetDefaultInitFields(typeof(Table), "IsSystemObject");
                sqlServer.SetDefaultInitFields(typeof(View), "Name");
                sqlServer.SetDefaultInitFields(typeof(View), "Schema");
                sqlServer.SetDefaultInitFields(typeof(View), "IsSystemObject");
                sqlServer.SetDefaultInitFields(typeof(StoredProcedure), "Name");
                sqlServer.SetDefaultInitFields(typeof(StoredProcedure), "Schema");
                sqlServer.SetDefaultInitFields(typeof(StoredProcedure), "IsSystemObject");
                sqlServer.SetDefaultInitFields(typeof(UserDefinedFunction), "Name");
                sqlServer.SetDefaultInitFields(typeof(UserDefinedFunction), "Schema");
                sqlServer.SetDefaultInitFields(typeof(UserDefinedFunction), "IsSystemObject");

                mdDatabase database = repository.GetDatabase(Execution, connectionStringBuilder.ConnectionString);

                sqlDatabase = sqlServer.Databases[connectionStringBuilder.InitialCatalog];
                DependencyWalker findDepenency = new DependencyWalker(sqlServer);
                List<SqlSmoObject> walkerList = new List<SqlSmoObject>();
                ScriptingOptions scriptOptions = new ScriptingOptions();
                scriptOptions.AllowSystemObjects = false;

                #region Enumerate Tables
                Console.WriteLine("Enumerate the Tables and their Dependencies");
                // Enumerate the Tables
                foreach (Table sqlTable in sqlDatabase.Tables)
                {
                    if (!sqlTable.IsSystemObject)
                    {
                        walkerList.Add(sqlTable);
                    }
                }

                WalkItem(sqlDatabase, database, walkerList, findDepenency, false);
                #endregion
                #region Enumerate Views
                Console.WriteLine("Enumerate the Views and their Dependencies");
                walkerList.Clear();
                foreach (View sqlView in sqlDatabase.Views)
                {
                    if (!sqlView.IsSystemObject)
                    {
                        walkerList.Add(sqlView);
                    }
                }

                WalkItem(sqlDatabase, database, walkerList, findDepenency, false);
                #endregion
                #region Enumerate Stored Procs
                Console.WriteLine("Enumerate the StoredProcedures and their Dependencies");
                walkerList.Clear();
                foreach (StoredProcedure sqlProcedure in sqlDatabase.StoredProcedures)
                {
                    if (!sqlProcedure.IsSystemObject)
                    {
                        walkerList.Add(sqlProcedure);
                    }
                }

                WalkItem(sqlDatabase, database, walkerList, findDepenency, false);
                #endregion
                #region Enumerate UDFs

                Console.WriteLine("Enumerate the UserDefinedFunction and their Dependencies");

                walkerList.Clear();
                foreach (UserDefinedFunction sqlFunctions in sqlDatabase.UserDefinedFunctions)
                {
                    if (!sqlFunctions.IsSystemObject)
                    {
                        walkerList.Add(sqlFunctions);
                    }
                }

                WalkItem(sqlDatabase, database, walkerList, findDepenency, false);
                #endregion
            }
            catch (Exception err)
            {
                Console.WriteLine(string.Format("Could not enumerate the database: {0}", err.Message));
            }
        }


        private void WalkItem(Database sqlDatabase, mdDatabase database, List<SqlSmoObject> walkerList, DependencyWalker findDepenency, Boolean reversed)
        {
            SqlSmoObject[] walkerArray;
            int arrayCounter = 0;
            DependencyTree dependTree;
            DependencyTreeNode walkNode, parentNode;
            mdObject tableObject, parentObject;
            string objectName, parentName;

            walkerArray = new SqlSmoObject[walkerList.Count];
            foreach (SqlSmoObject smoObject in walkerList)
            {
                walkerArray[arrayCounter++] = smoObject;
            }

            dependTree = findDepenency.DiscoverDependencies(walkerArray, DependencyType.Parents);
            // Walk the tree
            parentNode = dependTree.FirstChild;
            do
            {
                objectName = "[" + parentNode.Urn.GetAttribute("Schema") + "].[" + parentNode.Urn.GetAttribute("Name") + "]";
                tableObject = repository.GetObject(database, parentNode.Urn.GetAttribute("Schema"), parentNode.Urn.GetAttribute("Name"), parentNode.Urn.Type);
                if (parentNode.HasChildNodes)
                {
                    // Point at the first dependency
                    walkNode = parentNode.FirstChild;
                    do
                    {
                        parentName = "[" + walkNode.Urn.GetAttribute("Schema") + "].[" + walkNode.Urn.GetAttribute("Name") + "]";
                        switch (walkNode.Urn.Type)
                        {
                            case "UnresolvedEntity":
                                if (sqlDatabase.StoredProcedures.Contains(walkNode.Urn.GetAttribute("Name"), walkNode.Urn.GetAttribute("Schema")))
                                {
                                    parentObject = repository.GetObject(database, walkNode.Urn.GetAttribute("Schema"), walkNode.Urn.GetAttribute("Name"), "StoredProcedure");
                                }
                                else if (sqlDatabase.UserDefinedFunctions.Contains(walkNode.Urn.GetAttribute("Name"), walkNode.Urn.GetAttribute("Schema")))
                                {
                                    parentObject = repository.GetObject(database, walkNode.Urn.GetAttribute("Schema"), walkNode.Urn.GetAttribute("Name"), "UserDefinedFunction");
                                }
                                else
                                {
                                    Console.WriteLine("Warning: Unresolvable Dependency encountered in object {0} for object {1} of type {2}.\r\nYour database may have some problems.", objectName, parentName, walkNode.Urn.Type);
                                    parentObject = repository.GetObject(database, walkNode.Urn.GetAttribute("Schema"), walkNode.Urn.GetAttribute("Name"), walkNode.Urn.Type);
                                }
                                break;
                            case "Table":
                            case "View":
                            case "UserDefinedFunction":
                            case "StoredProcedure":
                                parentObject = repository.GetObject(database, walkNode.Urn.GetAttribute("Schema"), walkNode.Urn.GetAttribute("Name"), walkNode.Urn.Type);
                                break;
                            case "PartitionScheme":
                            case "UserDefinedDataType":
                            case "XmlSchemaCollection":
                                parentObject = null;
                                break;
                            default:
                                Console.WriteLine("Warning: Unrecognised object type encountered in object {0} of type {1}.\r\nData has been saved to repository.\r\nPlease report to http://ssismhash.codeplex.com", objectName, walkNode.Urn.Type);
                                parentObject = repository.GetObject(database, walkNode.Urn.GetAttribute("Schema"), walkNode.Urn.GetAttribute("Name"), walkNode.Urn.Type);
                                break;
                        }
                        if (parentObject != null)
                            if (reversed)
                                repository.GetObjectMap(tableObject, parentObject, false);
                            else
                                repository.GetObjectMap(parentObject, tableObject, false);
                        walkNode = walkNode.NextSibling;
                    }
                    while (walkNode != null);
                }
                parentNode = parentNode.NextSibling;
            }
            while (parentNode != null);
        }
    }
}
